-- 字典功能数据库表创建脚本 (PostgreSQL版本)
-- 创建时间: 2024-01-01
-- 作者: fudaopin

-- 1. 字典类型表
CREATE TABLE IF NOT EXISTS dict_type (
    id VARCHAR(32) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(500),
    enabled INTEGER DEFAULT 0,
    sort INTEGER DEFAULT 0,
    icon VARCHAR(100),
    color VARCHAR(20),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    remark VARCHAR(500)
);

-- 添加表注释
COMMENT ON TABLE dict_type IS '字典类型表';
COMMENT ON COLUMN dict_type.id IS '主键ID';
COMMENT ON COLUMN dict_type.name IS '字典类型名称';
COMMENT ON COLUMN dict_type.code IS '字典类型编码';
COMMENT ON COLUMN dict_type.description IS '字典类型描述';
COMMENT ON COLUMN dict_type.enabled IS '是否启用';
COMMENT ON COLUMN dict_type.sort IS '排序';
COMMENT ON COLUMN dict_type.icon IS '图标';
COMMENT ON COLUMN dict_type.color IS '颜色';
COMMENT ON COLUMN dict_type.create_date IS '创建时间';
COMMENT ON COLUMN dict_type.update_date IS '更新时间';
COMMENT ON COLUMN dict_type.create_by IS '创建人';
COMMENT ON COLUMN dict_type.update_by IS '更新人';
COMMENT ON COLUMN dict_type.remark IS '备注';

-- 2. 字典数据表
CREATE TABLE IF NOT EXISTS dict_data (
    id VARCHAR(32) PRIMARY KEY,
    type_id VARCHAR(32) NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL,
    value VARCHAR(100) NOT NULL,
    enabled INTEGER DEFAULT 0,
    sort INTEGER DEFAULT 0,
    icon VARCHAR(100),
    color VARCHAR(20),
    description VARCHAR(500),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    create_by VARCHAR(50),
    update_by VARCHAR(50),
    remark VARCHAR(500),
    CONSTRAINT fk_dict_data_type_id FOREIGN KEY (type_id) REFERENCES dict_type(id) ON DELETE CASCADE
);

-- 添加表注释
COMMENT ON TABLE dict_data IS '字典数据表';
COMMENT ON COLUMN dict_data.id IS '主键ID';
COMMENT ON COLUMN dict_data.type_id IS '字典类型ID';
COMMENT ON COLUMN dict_data.name IS '字典数据名称';
COMMENT ON COLUMN dict_data.code IS '字典数据编码';
COMMENT ON COLUMN dict_data.value IS '字典数据值';
COMMENT ON COLUMN dict_data.enabled IS '是否启用';
COMMENT ON COLUMN dict_data.sort IS '排序';
COMMENT ON COLUMN dict_data.icon IS '图标';
COMMENT ON COLUMN dict_data.color IS '颜色';
COMMENT ON COLUMN dict_data.description IS '描述';
COMMENT ON COLUMN dict_data.create_date IS '创建时间';
COMMENT ON COLUMN dict_data.update_date IS '更新时间';
COMMENT ON COLUMN dict_data.create_by IS '创建人';
COMMENT ON COLUMN dict_data.update_by IS '更新人';
COMMENT ON COLUMN dict_data.remark IS '备注';

-- 3. 创建索引
CREATE INDEX IF NOT EXISTS idx_dict_type_code ON dict_type(code);
CREATE INDEX IF NOT EXISTS idx_dict_type_enabled ON dict_type(enabled);
CREATE INDEX IF NOT EXISTS idx_dict_data_type_id ON dict_data(type_id);
CREATE INDEX IF NOT EXISTS idx_dict_data_code ON dict_data(code);
CREATE INDEX IF NOT EXISTS idx_dict_data_enabled ON dict_data(enabled);
CREATE INDEX IF NOT EXISTS idx_dict_data_value ON dict_data(value);

-- 4. 创建更新时间触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_date = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 5. 创建触发器
DROP TRIGGER IF EXISTS update_dict_type_updated_at ON dict_type;
CREATE TRIGGER update_dict_type_updated_at
    BEFORE UPDATE ON dict_type
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_dict_data_updated_at ON dict_data;
CREATE TRIGGER update_dict_data_updated_at
    BEFORE UPDATE ON dict_data
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 6. 插入初始数据

-- 插入字典类型数据
INSERT INTO dict_type (id, name, code, description, enabled, sort, icon, color) VALUES
('DT001', '支付方式', 'PAYMENT_WAY', '账单支付方式字典', 0, 1, 'eva:credit-card-outline', '#1976d2'),
('DT002', '收入来源', 'INCOME_SOURCE', '收入来源字典', 0, 2, 'eva:trending-up-fill', '#4caf50'),
('DT003', '账单类型', 'BILL_TYPE', '账单类型字典', 0, 3, 'eva:file-text-fill', '#ff9800'),
('DT004', '支付平台', 'PAYMENT_PLATFORM', '支付平台字典', 0, 4, 'eva:smartphone-fill', '#9c27b0')
ON CONFLICT (code) DO NOTHING;

-- 插入支付方式数据
INSERT INTO dict_data (id, type_id, name, code, value, enabled, sort, icon, color) VALUES
('DD001', 'DT001', '现金', 'CASH', '现金', 0, 1, 'eva:credit-card-outline', '#1976d2'),
('DD002', 'DT001', '银行卡', 'BANK_CARD', '银行卡', 0, 2, 'eva:credit-card-outline', '#1976d2'),
('DD003', 'DT001', '微信支付', 'WECHAT', '微信支付', 0, 3, 'eva:credit-card-outline', '#1976d2'),
('DD004', 'DT001', '支付宝', 'ALIPAY', '支付宝', 0, 4, 'eva:credit-card-outline', '#1976d2'),
('DD005', 'DT001', '银行转账', 'BANK_TRANSFER', '银行转账', 0, 5, 'eva:credit-card-outline', '#1976d2'),
('DD006', 'DT001', '其他', 'OTHER', '其他', 0, 6, 'eva:credit-card-outline', '#1976d2')
ON CONFLICT (id) DO NOTHING;

-- 插入收入来源数据
INSERT INTO dict_data (id, type_id, name, code, value, enabled, sort, icon, color) VALUES
('DD007', 'DT002', '工资收入', 'SALARY', '工资收入', 0, 1, 'eva:trending-up-fill', '#4caf50'),
('DD008', 'DT002', '奖金收入', 'BONUS', '奖金收入', 0, 2, 'eva:trending-up-fill', '#4caf50'),
('DD009', 'DT002', '投资收益', 'INVESTMENT', '投资收益', 0, 3, 'eva:trending-up-fill', '#4caf50'),
('DD010', 'DT002', '兼职收入', 'PART_TIME', '兼职收入', 0, 4, 'eva:trending-up-fill', '#4caf50'),
('DD011', 'DT002', '经营收入', 'BUSINESS', '经营收入', 0, 5, 'eva:trending-up-fill', '#4caf50'),
('DD012', 'DT002', '租金收入', 'RENTAL', '租金收入', 0, 6, 'eva:trending-up-fill', '#4caf50'),
('DD013', 'DT002', '利息收入', 'INTEREST', '利息收入', 0, 7, 'eva:trending-up-fill', '#4caf50'),
('DD014', 'DT002', '分红收入', 'DIVIDEND', '分红收入', 0, 8, 'eva:trending-up-fill', '#4caf50'),
('DD015', 'DT002', '礼金收入', 'GIFT', '礼金收入', 0, 9, 'eva:trending-up-fill', '#4caf50'),
('DD016', 'DT002', '退款收入', 'REFUND', '退款收入', 0, 10, 'eva:trending-up-fill', '#4caf50'),
('DD017', 'DT002', '其他收入', 'OTHER_INCOME', '其他收入', 0, 11, 'eva:trending-up-fill', '#4caf50')
ON CONFLICT (id) DO NOTHING;

-- 插入账单类型数据
INSERT INTO dict_data (id, type_id, name, code, value, enabled, sort, icon, color) VALUES
('DD018', 'DT003', '收入', 'INCOME', '收入', 0, 1, 'eva:file-text-fill', '#4caf50'),
('DD019', 'DT003', '工资', 'SALARY', '工资', 0, 2, 'eva:file-text-fill', '#4caf50'),
('DD020', 'DT003', '奖金', 'BONUS', '奖金', 0, 3, 'eva:file-text-fill', '#4caf50'),
('DD021', 'DT003', '投资收益', 'INVESTMENT', '投资收益', 0, 4, 'eva:file-text-fill', '#4caf50'),
('DD022', 'DT003', '退款', 'REFUND', '退款', 0, 5, 'eva:file-text-fill', '#4caf50'),
('DD023', 'DT003', '兼职收入', 'PART_TIME', '兼职收入', 0, 6, 'eva:file-text-fill', '#4caf50'),
('DD024', 'DT003', '经营收入', 'BUSINESS', '经营收入', 0, 7, 'eva:file-text-fill', '#4caf50'),
('DD025', 'DT003', '租金收入', 'RENTAL', '租金收入', 0, 8, 'eva:file-text-fill', '#4caf50'),
('DD026', 'DT003', '利息收入', 'INTEREST', '利息收入', 0, 9, 'eva:file-text-fill', '#4caf50'),
('DD027', 'DT003', '分红收入', 'DIVIDEND', '分红收入', 0, 10, 'eva:file-text-fill', '#4caf50'),
('DD028', 'DT003', '礼金收入', 'GIFT', '礼金收入', 0, 11, 'eva:file-text-fill', '#4caf50'),
('DD029', 'DT003', '其他收入', 'OTHER_INCOME', '其他收入', 0, 12, 'eva:file-text-fill', '#4caf50'),
('DD030', 'DT003', '支出', 'EXPENSE', '支出', 0, 13, 'eva:file-text-fill', '#f44336'),
('DD031', 'DT003', '餐饮', 'FOOD', '餐饮', 0, 14, 'eva:file-text-fill', '#f44336'),
('DD032', 'DT003', '交通', 'TRANSPORT', '交通', 0, 15, 'eva:file-text-fill', '#f44336'),
('DD033', 'DT003', '购物', 'SHOPPING', '购物', 0, 16, 'eva:file-text-fill', '#f44336'),
('DD034', 'DT003', '娱乐', 'ENTERTAINMENT', '娱乐', 0, 17, 'eva:file-text-fill', '#f44336'),
('DD035', 'DT003', '住房', 'HOUSING', '住房', 0, 18, 'eva:file-text-fill', '#f44336'),
('DD036', 'DT003', '水电费', 'UTILITIES', '水电费', 0, 19, 'eva:file-text-fill', '#f44336'),
('DD037', 'DT003', '医疗', 'MEDICAL', '医疗', 0, 20, 'eva:file-text-fill', '#f44336'),
('DD038', 'DT003', '教育', 'EDUCATION', '教育', 0, 21, 'eva:file-text-fill', '#f44336'),
('DD039', 'DT003', '保险', 'INSURANCE', '保险', 0, 22, 'eva:file-text-fill', '#f44336'),
('DD040', 'DT003', '投资支出', 'INVESTMENT_EXPENSE', '投资支出', 0, 23, 'eva:file-text-fill', '#f44336'),
('DD041', 'DT003', '其他支出', 'OTHER_EXPENSE', '其他支出', 0, 24, 'eva:file-text-fill', '#f44336')
ON CONFLICT (id) DO NOTHING;

-- 插入支付平台数据
INSERT INTO dict_data (id, type_id, name, code, value, enabled, sort, icon, color) VALUES
('DD042', 'DT004', '工商银行', 'ICBC', '工商银行', 0, 1, 'eva:smartphone-fill', '#9c27b0'),
('DD043', 'DT004', '建设银行', 'CCB', '建设银行', 0, 2, 'eva:smartphone-fill', '#9c27b0'),
('DD044', 'DT004', '农业银行', 'ABC', '农业银行', 0, 3, 'eva:smartphone-fill', '#9c27b0'),
('DD045', 'DT004', '中国银行', 'BOC', '中国银行', 0, 4, 'eva:smartphone-fill', '#9c27b0'),
('DD046', 'DT004', '招商银行', 'CMB', '招商银行', 0, 5, 'eva:smartphone-fill', '#9c27b0'),
('DD047', 'DT004', '微信', 'WECHAT', '微信', 0, 6, 'eva:smartphone-fill', '#9c27b0'),
('DD048', 'DT004', '支付宝', 'ALIPAY', '支付宝', 0, 7, 'eva:smartphone-fill', '#9c27b0'),
('DD049', 'DT004', '其他', 'OTHER', '其他', 0, 8, 'eva:smartphone-fill', '#9c27b0')
ON CONFLICT (id) DO NOTHING;
